package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.soda.bean.Student;
import com.soda.dao.BaseDao;
import com.soda.dao.StudentDao;
import com.soda.service.StudentService;
import com.soda.service.impl.StudentServiceImpl;
import com.soda.util.PageInfo;
import com.soda.util.SelectStudentCondition;

public class StudentDaoImpl extends BaseDao implements StudentDao {

	/**
	 * 宗矿帅
	 * 
	 * 查询登录时判断是否存在此用户
	 */
	public Student selectstuNameByLoginNamePwd(String name, String pwd) {
		try {
			getConnection();
			String sql = "select * from student where stu_loginName=? and stu_password=? and stu_delete=0";
			pt = conn.prepareStatement(sql);
			pt.setString(1, name);
			pt.setString(2, pwd);
			res = pt.executeQuery();
			if (res.next()) {
				Student s = new Student();
				s.setStuId(res.getInt(1));
				s.setStuLoginname(res.getString(2));
				s.setStuPassword(res.getString(3));
				s.setStuName(res.getString(4));
				s.setStuSex(res.getInt(5));
				s.setStuAge(res.getInt(6));
				s.setStuSchool(res.getString(7));
				s.setStuIdcard(res.getString(8));
				s.setStuMajor(res.getString(9));
				s.setStuPhone(res.getString(10));
				s.setStuAddress(res.getString(11));
				s.setStuEmail(res.getString(12));
				s.setStuRemark(res.getString(13));
				s.setStuMoney(res.getInt(14));
				s.setStuDelete(res.getInt(15));
				return s;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	/**
	 * 宗矿帅
	 * 
	 * 查询注册时ajax传的值判断此用户名是否已经存在
	 */
	public int selectajaxName(String name) {
		try {
			getConnection();
			String sql = "select * from student where stu_loginName=?";
			pt = conn.prepareStatement(sql);
			pt.setString(1, name);
			res = pt.executeQuery();
			if (res.next()) {
				return 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	/**
	 * 宗矿帅
	 * 添加注册学生信息
	 */
	public int addRegistStuMessage(Student s) {
		try {
			getConnection();
			String sql = "insert into student values(null,?,?,?,?,20,?,?,?,?,?,?,?,0,0)";
			pt = conn.prepareStatement(sql);
			pt.setString(1, s.getStuLoginname());
			pt.setString(2, s.getStuPassword());
			pt.setString(3, s.getStuName());
			pt.setInt(4, s.getStuSex());
			pt.setString(5, s.getStuSchool());
			pt.setString(6, s.getStuIdcard());
			pt.setString(7, s.getStuMajor());
			pt.setString(8, s.getStuPhone());
			pt.setString(9, s.getStuAddress());
			pt.setString(10, s.getStuEmail());
			pt.setString(11, s.getStuRemark());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	/**
	 * 查询个人信息
	 */
	public Student selectStumessage(int stuid) {
		try {
			getConnection();
			String sql = "select * from student where stu_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, stuid);
			res = pt.executeQuery();
			if (res.next()) {
				Student s = new Student();
				s.setStuId(res.getInt(1));
				s.setStuLoginname(res.getString(2));
				s.setStuPassword(res.getString(3));
				s.setStuName(res.getString(4));
				s.setStuSex(res.getInt(5));
				s.setStuAge(res.getInt(6));
				s.setStuSchool(res.getString(7));
				s.setStuIdcard(res.getString(8));
				s.setStuMajor(res.getString(9));
				s.setStuPhone(res.getString(10));
				s.setStuAddress(res.getString(11));
				s.setStuEmail(res.getString(12));
				s.setStuRemark(res.getString(13));
				s.setStuMoney(res.getInt(14));
				s.setStuDelete(res.getInt(15));
				return s;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	/**
	 * 宗矿帅
	 * 
	 * 充值钱
	 */
	public int chongzhiMoney(Student student, int money) {
		try {
			getConnection();
			String sql = "update student set stu_money=stu_money+? where stu_id=? ";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, money);
			pt.setInt(2, student.getStuId());

			return pt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	/**
	 * 宗矿帅
	 * 
	 * 查询充值后的钱数
	 */
	public Student selectMoneyChongZhiHou(int id) {
		try {
			getConnection();
			String sql = "select * from student where stu_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			if (res.next()) {
				Student s = new Student();
				s.setStuId(res.getInt(1));
				s.setStuLoginname(res.getString(2));
				s.setStuPassword(res.getString(3));
				s.setStuName(res.getString(4));
				s.setStuSex(res.getInt(5));
				s.setStuAge(res.getInt(6));
				s.setStuSchool(res.getString(7));
				s.setStuIdcard(res.getString(8));
				s.setStuMajor(res.getString(9));
				s.setStuPhone(res.getString(10));
				s.setStuAddress(res.getString(11));
				s.setStuEmail(res.getString(12));
				s.setStuRemark(res.getString(13));
				s.setStuMoney(res.getInt(14));
				s.setStuDelete(res.getInt(15));
				return s;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
/**
 * 修改密码
 */
	public int updatePwd(int stuid, String newpwd) {
		try {
			getConnection();
			String sql = "update student set stu_password=? where stu_id=? ";
			pt = conn.prepareStatement(sql);
			pt.setString(1, newpwd);
			pt.setInt(2, stuid);

			return pt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

public int updateMessage(int stuid, Student s) {
	try {
		getConnection();
		String sql = "update student set stu_school=?,stu_major=?,stu_phone=?,stu_address=?,stu_email=?,stu_remark=? where stu_id=?";
		pt = conn.prepareStatement(sql);
		pt.setString(1, s.getStuSchool());
		pt.setString(2, s.getStuMajor());
		pt.setString(3, s.getStuPhone());
		pt.setString(4, s.getStuAddress());
		pt.setString(5, s.getStuEmail());
		pt.setString(6, s.getStuRemark());
		pt.setInt(7, stuid);
		return pt.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		close();
	}
	return 0;
}

/**
 * 后台学生全查
 */
public List<Student> selectAllStuForHouTai(PageInfo pi) {
	try {
		getConnection();
		String sql = "select * from student where stu_delete=0 limit ?,? ";
		pt = conn.prepareStatement(sql);	
		pt.setInt(1, (pi.getPage()-1)*pi.getPer());
		pt.setInt(2, pi.getPer());
		res = pt.executeQuery();
		 List<Student> list=new ArrayList<Student>();
		while (res.next()) {
			Student s = new Student();
			s.setStuId(res.getInt(1));
			s.setStuLoginname(res.getString(2));
			s.setStuPassword(res.getString(3));
			s.setStuName(res.getString(4));
			s.setStuSex(res.getInt(5));
			s.setStuAge(res.getInt(6));
			s.setStuSchool(res.getString(7));
			s.setStuIdcard(res.getString(8));
			s.setStuMajor(res.getString(9));
			s.setStuPhone(res.getString(10));
			s.setStuAddress(res.getString(11));
			s.setStuEmail(res.getString(12));
			s.setStuRemark(res.getString(13));
			s.setStuMoney(res.getInt(14));
			s.setStuDelete(res.getInt(15));
			list.add(s);
		}
		
		return list;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		close();
	}
	return null;
}

public int queryTotal() {
	try {
		getConnection();
		String sql = "select count(1) from student  where stu_delete=0";
		pt = conn.prepareStatement(sql);
		res=pt.executeQuery();
		if(res.next()){
			return res.getInt(1);
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		close();
	}
	return 0;
}

public List<Student> selectStuByCondi(PageInfo pi, SelectStudentCondition ssc) {
	try {
		getConnection();
		System.out.println("1");
		StringBuffer buffer=new StringBuffer("select * from student where 1=1 and stu_delete=0");
		if(ssc.getStuname()!=null&&ssc.getStuname()!=""){
			buffer.append(" and stu_name like ? ");
		}
		if(ssc.getIdcard()!=null&&ssc.getIdcard()!=""){
			buffer.append(" and stu_idcard like ? ");
		}
		buffer.append(" limit ?,? ");
		pt=conn.prepareStatement(buffer.toString());
		int x=1;
		if(ssc.getStuname()!=null&&ssc.getStuname()!=""){
			pt.setString(x, ssc.getStuname()+"%");
			x++;
		}
		if(ssc.getIdcard()!=null&&ssc.getIdcard()!=""){
			pt.setString(x, ssc.getIdcard()+"%");
			x++;
		}
		pt.setInt(x, (pi.getPage()-1)*pi.getPer());
		x++;
		pt.setInt(x, pi.getPer());
		x++;
		res=pt.executeQuery();
		List<Student> list=new ArrayList<Student>();
		while(res.next()){
			Student s = new Student();
			s.setStuId(res.getInt(1));
			s.setStuLoginname(res.getString(2));
			s.setStuPassword(res.getString(3));
			s.setStuName(res.getString(4));
			s.setStuSex(res.getInt(5));
			s.setStuAge(res.getInt(6));
			s.setStuSchool(res.getString(7));
			s.setStuIdcard(res.getString(8));
			s.setStuMajor(res.getString(9));
			s.setStuPhone(res.getString(10));
			s.setStuAddress(res.getString(11));
			s.setStuEmail(res.getString(12));
			s.setStuRemark(res.getString(13));
			s.setStuMoney(res.getInt(14));
			s.setStuDelete(res.getInt(15));
			list.add(s);
		}
		System.out.println("3");
		return list;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}
	return null;
}

public int queryConTotal(SelectStudentCondition ssc) {

	try {
		getConnection();
		StringBuffer buffer=new StringBuffer("select count(1) from student where 1=1 and stu_delete=0 ");
		if(ssc.getStuname()!=null&&ssc.getStuname()!=""){
			buffer.append(" and stu_name like ? ");
		}
		if(ssc.getIdcard()!=null&&ssc.getIdcard()!=""){
			buffer.append(" and stu_idcard like ? ");
		}
		pt=conn.prepareStatement(buffer.toString());
		int x=1;
		if(ssc.getStuname()!=null&&ssc.getStuname()!=""){
			pt.setString(x, ssc.getStuname()+"%");
			x++;
		}
		if(ssc.getIdcard()!=null&&ssc.getIdcard()!=""){
			pt.setString(x, ssc.getIdcard()+"%");
			x++;
		}
		
		res=pt.executeQuery();
		if(res.next()){
			return res.getInt(1);	
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}
	
	
	return 0;
}

public Student selectStuById(int stuid) {
	try {
		getConnection();
		String sql = "select * from student where stu_id=?";
		pt = conn.prepareStatement(sql);
		pt.setInt(1, stuid);
		res = pt.executeQuery();
		if (res.next()) {
			Student s = new Student();
			s.setStuId(res.getInt(1));
			s.setStuLoginname(res.getString(2));
			s.setStuPassword(res.getString(3));
			s.setStuName(res.getString(4));
			s.setStuSex(res.getInt(5));
			s.setStuAge(res.getInt(6));
			s.setStuSchool(res.getString(7));
			s.setStuIdcard(res.getString(8));
			s.setStuMajor(res.getString(9));
			s.setStuPhone(res.getString(10));
			s.setStuAddress(res.getString(11));
			s.setStuEmail(res.getString(12));
			s.setStuRemark(res.getString(13));
			s.setStuMoney(res.getInt(14));
			s.setStuDelete(res.getInt(15));
			return s;
		}

	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		close();
	}
	return null;
}

public int updateHoutaiStu(int stuid, Student s) {
	try {
		getConnection();
		String sql="update student set stu_password=?,stu_name=?,stu_sex=?,stu_school=?,stu_idcard=?,"
				+ "stu_major=?,stu_phone=?,stu_address=?,stu_email=?,stu_remark=? where stu_id=?";
		pt=conn.prepareStatement(sql);
		pt.setString(1, s.getStuPassword());
		pt.setString(2, s.getStuName());
		pt.setInt(3, s.getStuSex());
		pt.setString(4, s.getStuSchool());
		pt.setString(5, s.getStuIdcard());
		pt.setString(6, s.getStuMajor());
		pt.setString(7, s.getStuPhone());
		pt.setString(8, s.getStuAddress());
		pt.setString(9, s.getStuEmail());
		pt.setString(10, s.getStuRemark());
		pt.setInt(11, s.getStuId());
		return pt.executeUpdate();	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}
	return 0;
}

public int deleteStu(int stuid) {
	try {
		getConnection();
		String sql="update student set stu_delete=1 where stu_id=?";
		pt=conn.prepareStatement(sql);
		pt.setInt(1, stuid);
		return pt.executeUpdate();	
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}
	return 0;
}




}
